IF OBJECT_ID('dbo.RptPCROAgreementsUnderNegotiation') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptPCROAgreementsUnderNegotiation
    IF OBJECT_ID('dbo.RptPCROAgreementsUnderNegotiation') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptPCROAgreementsUnderNegotiation >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptPCROAgreementsUnderNegotiation >>>'
END
go
-- =======================================================================  
-- Procedure Name: dbo.RptPCROAgreementsUnderNegotiation  
--  
-- Author:      Kevin Cashman, Parallax Consulting LLC  
--  
-- Create date: 08/18/2008  
--  
-- Description: Source of Report, "PCROAgreementsUnderNegotiation"  
--                  In Insight DB Reporting Services  
--  
-- Report is a quarterly report from PCRO that lists all agreements currently  
-- being negotiated by PCRO.   
--  
-- Parameters:    
--             @StartDate            
--             @EndDate              
--             @CSCRCaseManager           
--             @Institution       
--             @Department  
--             @Unit       
--  
--  
-- Returns:     INT, 0=Success, -1=Error  
--  
-- SampleCall:  
-- EXECUTE dbo.RptPCROAgreementsUnderNegotiation()  
--  
--------------------------------------------------------------------------  
-- Date        Initials  Modification  
--------------------------------------------------------------------------  
-- 08/18/08    KC        Created  
-- 08/19/08    KC        Changed date search and dropped History (REX) search.  
-- 08/27/08    KC        Dropped Start and End Date, Get Current Status  
--  
-- =======================================================================  

CREATE PROCEDURE dbo.RptPCROAgreementsUnderNegotiation  
     (  
     @CSCRCaseManager                   VARCHAR(100) = NULL,  
     @Institution                       VARCHAR(100) = NULL,               
     @Department                        VARCHAR(100) = NULL,  
     @Unit                              VARCHAR(100) = NULL,  
     @IncludeDanaFarber                 VARCHAR(3) = NULL,  
     @DomainUserId                      INT  
     )   
                                     
AS  
BEGIN  
  
     -- Initial Settings  
     SET NOCOUNT ON  
       
     -- Declare Local Variables  
     DECLARE   @Error INT,  
               @RowCount INT,  
               @ConversionDate DATETIME  
  
     -- Default this, Records before this PropStatDate(s) will not be returned  
     SET  @ConversionDate = '8/30/2007'  
                 
     -- ALL checking  
     IF @CSCRCaseManager IN ('ALL','','0') SET @CSCRCaseManager = NULL  
     IF @Institution IN ('ALL','','0') SET @Institution = NULL  
     IF @Department IN ('ALL','','0') SET @Department = NULL  
     IF @Unit IN ('ALL','','0') SET @Unit = NULL  
     IF @IncludeDanaFarber IN ('Yes','1') SET @IncludeDanaFarber = NULL ELSE SET @IncludeDanaFarber = '2'  
       
     -- Result Set  
     SELECT    AI.AgreementId AS AgreementId,  
               AI.FolderNumber AS FolderNumber,  
               AI.InfoEdPropNumber AS InfoEdPropNumber,  
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,  
               AI.PrincipalInvestigator AS PrincipalInvestigator,  
               AI.CSCRCaseManager AS CSCRCaseManager,  
               AI.CSCRCaseManagerName AS CSCRCaseManagerName,  
               AI.ProposalShortTitle AS AgreementShortTitle,  
               AI.SponsorName AS PrimarySponsor,  
               CASE WHEN CHARINDEX('(',AI.SubActivityType) > 0  
                    THEN SUBSTRING(AI.SubActivityType,1,CHARINDEX('(',AI.SubActivityType) - 2)  
                    ELSE AI.SubActivityType  
               END AS SubActivityType,  
               AI.IRBApprovalDateFrom AS ApprovalDate,  
               AI.SecondarySponsor AS SecondarySponsor,  
               AI.InstrumentType AS SubAgreementType,            
               AI.MasterProposalStatus AS MasterAgreementStatus,  
               AI.ProposalStatus AS AgreementStatus,  
               AI.AgreementType AS AgreementType,  
               AI.InstrumentType AS InstrumentType,  
               AI.Institution AS Institution,  
               AI.Department AS Department,  
               AI.ChiefCode AS ChiefCode,  
               ISNULL(AI.Unit,'(None)') AS UnitCode,  
               AI.SponsorId AS SponsorId,  
               PROP.inst_no AS PropInstNo,  
               PROP.prop_no AS Prop_No,  
               PROP.FolderNumber AS PropFolderNumber,  
               PROP.TransactionType AS PropTransactionType,  
               PROP.PropStat AS PropStatus,  
               PROP.prop_stat AS PropStatusCode,  
               PROP.PropStatDate AS PropStatusDate,  
               PROP.Proj_Title AS PropTitle,  
			   SC.ReportName as DeliverableName,  
               SC.StatusDate as DatePending  
                 
                 
     FROM      dbo.AgrAgreement_Info AI INNER JOIN  
  
                    (SELECT  CASE WHEN CHARINDEX('-',VW.inst_no) > 0   
                                 THEN SUBSTRING(VW.inst_no,1,CHARINDEX('-',VW.inst_no) - 1)   
                                 ELSE VW.inst_no   
                            END AS FolderNumber,  
                            VW.inst_no,   
                            VW.prop_no,  
                            VW.prop_stat,  
                            VW.PropStat,  
                            VW.PropStatDate,  
                            VW.TransactionType,  
                            LEFT(VW.Proj_Title,255) AS Proj_Title  
                    FROM      dbo.vwInfoEdProposalsSrLeader VW  
                        INNER JOIN (SELECT  prop_no,  
                                            MAX(PropStatDate) AS LastPropStatDate  
                                    FROM    dbo.vwInfoEdProposalsSrLeader  
                                    WHERE   stat_type = 0  
                                            AND (TransactionType LIKE '%Amendment%' OR TransactionType LIKE '%Master%')  
                                            -- only PostConverted Records  
                                            AND dbo.fnRemoveTime(PropStatDate) > @ConversionDate  
                                    GROUP BY prop_no) LAST ON VW.prop_no = LAST.prop_no AND VW.PropStatDate = LAST.LastPropStatDate  
                    WHERE   VW.stat_type = 0 AND  
                        VW.prop_stat IN ('PARTNERS_P_PROP_STAT_65','PARTNERS_PD_PROP_STAT_PN','PARTNERS_P_BUD_STAT_1')   
                        AND (TransactionType LIKE '%Amendment%' OR TransactionType LIKE '%Master%')) PROP ON  
                        AI.FolderNumber = PROP.FolderNumber   
  
					 LEFT OUTER JOIN  
					 -- Get SubContract Name and Date when Status is Pending  
					 (  
					  SELECT D.prop_no AS prop_no, D.ReportID AS ReportID, D.ReportName AS ReportName ,MIN(S.StatusDate) AS StatusDate FROM  dbo.InfoEdPTDeliverable D   LEFT OUTER JOIN  
						dbo.InfoEdPTDeliverableStatus AS S ON D.DeliverableID = S.DeliverableID                            
						WHERE D.REPORTCAT='PARTNERS_P_REPORTCAT_34' AND S.REPORTSTAT='PARTNERS_P_REPORTSTAT_61'  
						GROUP BY D.prop_no, D.ReportID, D.ReportName   
					 ) SC  
					 ON AI.InfoEdPropNumber = SC.prop_no    
  
     WHERE     -- Definition of PCRO Agreements  
               ((RTRIM(AI.ActivityDescription) = 'Clinical Research' AND  
               RTRIM(AI.SponsorTypeName) = 'Industry' AND  
               RTRIM(AI.InstrumentType) = 'Contract' AND  
               RIGHT(RTRIM(AI.SubActivityType),3) = '(C)') OR  
                 
               (RTRIM(AI.ActivityDescription) = 'Clinical Research' AND  
               RTRIM(AI.SponsorTypeName) = 'Industry' AND  
               RTRIM(AI.SubActivityType) != '' AND  
               RTRIM(AI.InstrumentType) = 'Gift' )) AND  
                 
                 
               (AI.CSCRCaseManager IS NOT NULL AND RTRIM(AI.CSCRCaseManager) != '') AND  
                 
               -- Optional Search Criteria  
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND  
               (@Department IS NULL OR AI.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND  
               (@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND  
               (@CSCRCaseManager IS NULL OR AI.CSCRManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @CSCRCaseManager ))) AND  
  
           -- Change to Institution  
               (@IncludeDanaFarber IS NULL OR AI.InstitutionId NOT IN (350,351))                  
    
  
     ORDER BY AI.Institution, AI.Department, AI.PrincipalInvestigator  
  
  
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT  
     IF @Error!=0  
          RETURN -1  
            
     -- Return Success  
     RETURN 0  
  
       
END  
go

IF OBJECT_ID('dbo.RptPCROAgreementsUnderNegotiation') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptPCROAgreementsUnderNegotiation >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptPCROAgreementsUnderNegotiation >>>'
go

GRANT EXECUTE ON dbo.RptPCROAgreementsUnderNegotiation TO InsightReport,Insight
go